﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data; 
using System.Data.Common;
using WMSADBUtility;
using WMSAML.SY;

namespace WMSADL.SY
{
    public class TOrganization_DAL
    {
        /// <summary>
        /// 
        /// </summary>
        public int Add(TOrganization_Entity entity)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into TOrganization(");
            strSql.Append("Parent_id,Family,Code,Organization_name,English_Name,Short_Name,Creat_time,Description)");

            strSql.Append(" values (");
            strSql.Append("@Parent_id,@Family,@Code,@Organization_name,@English_Name,@Short_Name,@Creat_time,@Description)");
            strSql.Append(";select @@IDENTITY");
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "Parent_id", DbType.Int32, entity.Parent_id);
            db.AddInParameter(dbCommand, "Family", DbType.String, entity.Family);
            db.AddInParameter(dbCommand, "Code", DbType.String, entity.Code);
            db.AddInParameter(dbCommand, "Organization_name", DbType.String, entity.Organization_name);
            db.AddInParameter(dbCommand, "English_Name", DbType.String, entity.English_Name);
            db.AddInParameter(dbCommand, "Short_Name", DbType.String, entity.Short_Name);
            db.AddInParameter(dbCommand, "Creat_time", DbType.DateTime, entity.Creat_time);
            db.AddInParameter(dbCommand, "Description", DbType.String, entity.Description);
            int result;
            object obj = db.ExecuteScalar(dbCommand);
            if (!int.TryParse(obj.ToString(), out result))
            {
                return 0;
            }
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        public bool Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("DELETE FROM TOrganization WHERE Organization_id=@Organization_id ");
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "Organization_id", DbType.Int32, id);
                int rows = db.ExecuteNonQuery(dbCommand);

                if (rows > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        public bool Update(TOrganization_Entity entity)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE TOrganization SET ");
            strSql.Append("Family=@Family,");
            strSql.Append("Code=@Code,");
            strSql.Append("Organization_name=@Organization_name,");
            strSql.Append("English_Name=@English_Name,");
            strSql.Append("Short_Name=@Short_Name,");
            strSql.Append("Creat_time=@Creat_time,");
            strSql.Append("Description=@Description");
            strSql.Append(" WHERE Organization_id=@Organization_id ");
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "Organization_id", DbType.Int32, entity.Organization_id);
                db.AddInParameter(dbCommand, "Family", DbType.String, entity.Family);
                db.AddInParameter(dbCommand, "Code", DbType.String, entity.Code);
                db.AddInParameter(dbCommand, "Organization_name", DbType.String, entity.Organization_name);
                db.AddInParameter(dbCommand, "English_Name", DbType.String, entity.English_Name);
                db.AddInParameter(dbCommand, "Short_Name", DbType.String, entity.Short_Name);
                db.AddInParameter(dbCommand, "Creat_time", DbType.DateTime, entity.Creat_time);
                db.AddInParameter(dbCommand, "Description", DbType.String, entity.Description);
                int rows = db.ExecuteNonQuery(dbCommand);

                if (rows > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }
        /// <summary>
        /// ByID
        /// </summary>
        public bool ExistsByID(int id)
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT COUNT(1) FROM TOrganization WHERE Organization_id=@Organization_id ");
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "Organization_id", DbType.Int32, id);
                int cmdresult;
                object obj = db.ExecuteScalar(dbCommand);
                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                {
                    cmdresult = 0;
                }
                else
                {
                    cmdresult = int.Parse(obj.ToString());
                }
                if (cmdresult == 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
        }

        /// <summary>
        /// ByPID
        /// </summary>
        public bool ExistsByPID(int pid)
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT COUNT(1) FROM TOrganization WHERE Parent_id=@Parent_id ");
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "Parent_id", DbType.Int32, pid);
                int cmdresult;
                object obj = db.ExecuteScalar(dbCommand);
                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                {
                    cmdresult = 0;
                }
                else
                {
                    cmdresult = int.Parse(obj.ToString());
                }
                if (cmdresult == 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
        }

        /// <summary>
        /// ByName
        /// </summary>
        public bool ExistsByName(int organization_name)
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT COUNT(1) FROM TOrganization WHERE Organization_name=@Organization_name ");
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "Organization_name", DbType.String, organization_name);
                int cmdresult;
                object obj = db.ExecuteScalar(dbCommand);
                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                {
                    cmdresult = 0;
                }
                else
                {
                    cmdresult = int.Parse(obj.ToString());
                }
                if (cmdresult == 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
        }

        /// <summary>
        /// ByName
        /// </summary>
        /// <summary>
        public TOrganization_Entity GetEntityByName(string organization_name)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT Organization_id,Parent_id,Family,Code,Organization_name,English_Name,Short_Name,Creat_time,Description FROM TOrganization ");
            strSql.Append(" WHERE Organization_name=@Organization_name ");
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "Organization_name", DbType.String, organization_name);
                TOrganization_Entity entity = new TOrganization_Entity();
                using (IDataReader dataReader = db.ExecuteReader(dbCommand))
                {
                    if (dataReader.Read())
                    {
                        entity.Organization_id = Convert.ToInt32(dataReader["Organization_id"].ToString());
                        entity.Parent_id = Convert.ToInt32(dataReader["Parent_id"].ToString());
                        entity.Family = dataReader["Family"].ToString();
                        entity.Code = dataReader["Code"].ToString();
                        entity.Organization_name = dataReader["Organization_name"].ToString();
                        entity.English_Name = dataReader["English_Name"].ToString();
                        entity.Short_Name = dataReader["Short_Name"].ToString();
                        entity.Creat_time = Convert.ToDateTime(dataReader["Creat_time"].ToString());
                        entity.Description = dataReader["Description"].ToString();
                    }
                }
                return entity;
            }
        }

        /// <summary>
        /// ByID
        /// </summary>
        /// <summary>
        public TOrganization_Entity GetEntityByID(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT Organization_id,Parent_id,Family,Code,Organization_name,English_Name,Short_Name,Creat_time,Description FROM TOrganization ");
            strSql.Append(" WHERE Organization_id=@Organization_id ");
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "Organization_id", DbType.Int32, id);
                TOrganization_Entity entity = new TOrganization_Entity();
                using (IDataReader dataReader = db.ExecuteReader(dbCommand))
                {
                    if (dataReader.Read())
                    {
                        entity.Organization_id = Convert.ToInt32(dataReader["Organization_id"].ToString());
                        entity.Parent_id = Convert.ToInt32(dataReader["Parent_id"].ToString());
                        entity.Family = dataReader["Family"].ToString();
                        entity.Code = dataReader["Code"].ToString();
                        entity.Organization_name = dataReader["Organization_name"].ToString();
                        entity.English_Name = dataReader["English_Name"].ToString();
                        entity.Short_Name = dataReader["Short_Name"].ToString();
                        entity.Creat_time = Convert.ToDateTime(dataReader["Creat_time"].ToString());
                        entity.Description = dataReader["Description"].ToString();
                    }
                }
                return entity;
            }
        }

        /// <summary>
        /// ByParent_id
        /// </summary>
        /// <summary>
        public TOrganization_Entity GetEntityByParent_id(int parent_id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT Organization_id,Parent_id,Family,Code,Organization_name,English_Name,Short_Name,Creat_time,Description FROM TOrganization ");
            strSql.Append(" WHERE Parent_id=@Parent_id ORDER BY Family DESC ");
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "Parent_id", DbType.Int32, parent_id);
                TOrganization_Entity entity = new TOrganization_Entity();
                using (IDataReader dataReader = db.ExecuteReader(dbCommand))
                {
                    if (dataReader.Read())
                    {
                        entity.Organization_id = Convert.ToInt32(dataReader["Organization_id"].ToString());
                        entity.Parent_id = Convert.ToInt32(dataReader["Parent_id"].ToString());
                        entity.Family = dataReader["Family"].ToString();
                        entity.Code = dataReader["Code"].ToString();
                        entity.Organization_name = dataReader["Organization_name"].ToString();
                        entity.English_Name = dataReader["English_Name"].ToString();
                        entity.Short_Name = dataReader["Short_Name"].ToString();
                        entity.Creat_time = Convert.ToDateTime(dataReader["Creat_time"].ToString());
                        entity.Description = dataReader["Description"].ToString();
                    }
                }
                return entity;
            }
        } 

        /// <summary>
        /// 获取数据信息
        /// </summary>
        /// <returns>dataset</returns>
        public DataSet GetList_TOrganization()
        {
            Database db = DatabaseFactory.CreateDatabase();
            DataSet ds = new DataSet();
            using (DbCommand dbCommand = db.GetStoredProcCommand("ALPrcTree_TOrganization"))
            {
                ds = db.ExecuteDataSet(dbCommand);
                return (ds.Tables[0].Rows.Count < 1) ? null : ds;
            }
        }

        /// <summary>
        /// ByID视图
        /// </summary>
        /// <summary>
        public V_TOrganization_Entity GetEntityByIDV(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT Organization_id, Organization_name, Code, Family, Parent_id, ParentName, English_Name, Short_Name, Creat_time, Description, [level], paths ");
            strSql.Append("From Vlh_TOrganization ");
            strSql.Append("WHERE Organization_id=@Organization_id ");
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "Organization_id", DbType.Int32, id);
                V_TOrganization_Entity entity = new V_TOrganization_Entity();
                using (IDataReader dataReader = db.ExecuteReader(dbCommand))
                {
                    if (dataReader.Read())
                    {
                        entity.Organization_id = Convert.ToInt32(dataReader["Organization_id"].ToString());
                        entity.Organization_name = dataReader["Organization_name"].ToString();
                        entity.Parent_id = Convert.ToInt32(dataReader["Parent_id"].ToString());
                        entity.ParentName = dataReader["ParentName"].ToString();
                        entity.Family = dataReader["Family"].ToString();
                        entity.Code = dataReader["Code"].ToString();
                        entity.English_Name = dataReader["English_Name"].ToString();
                        entity.Short_Name = dataReader["Short_Name"].ToString();
                        entity.Creat_time = Convert.ToDateTime(dataReader["Creat_time"].ToString());
                        entity.Description = dataReader["Description"].ToString();
                        entity.level =  Convert.ToInt32(dataReader["level"].ToString());
                        entity.paths = dataReader["paths"].ToString();
                    }
                }
                return entity;
            }
        }

    }
}
